package com.thkj.modules.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.thkj.common.baseDao.BaseDao;
import com.thkj.modules.user.entity.FaceStranger;


public interface FaceStrangerMapper extends BaseDao<FaceStranger> {

    /**
     * 按年查询每月
     *
     * @param individual_id
     * @param year
     * @return
     */
    @Select("SELECT  count(id) value,MONTH(alerm_time) nomth FROM thkj_face_stranger " +
            "WHERE YEAR(alerm_time)= #{year} " +
            "AND individual_id = #{individual_id} " +
            "GROUP BY MONTH(alerm_time) " +
            "ORDER BY MONTH(alerm_time) DESC")
    @Results(value = {
            @Result(column = "value", property = "value", javaType = Integer.class),
            @Result(column = "nomth", property = "nomth", javaType = Integer.class)
    })
    List<Map> queryByYear(@Param("individual_id") String individual_id, @Param("year") String year);


    /**
     * 按月查询每天
     *
     * @param individual_id
     * @param month
     * @return
     */
    @Select("SELECT  count(id) value, DAY(alerm_time) day FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND DATE_FORMAT(alerm_time,'%Y-%m') = #{month} " +
            "GROUP BY DATE(alerm_time) " +
            "ORDER BY DATE(alerm_time) DESC")
    @Results(value = {
            @Result(column = "value", property = "value", javaType = Integer.class),
            @Result(column = "day", property = "day", javaType = Integer.class)
    })
    List<Map> queryByMonth(@Param("individual_id") String individual_id, @Param("month") String month);


    /**
     * 查询本周
     *
     * @param individual_id
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT  count(id) value, DAYOFWEEK(alerm_time) week FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND YEARWEEK(date_format(alerm_time,'%Y-%m-%d')) = YEARWEEK(now())" +
            "GROUP BY DATE(alerm_time) " +
            "ORDER BY DATE(alerm_time) DESC")
    @Results(value = {
            @Result(column = "value", property = "value", javaType = Integer.class),
            @Result(column = "week", property = "week", javaType = Integer.class)
    })
    List<Map> queryByWeek(@Param("individual_id") String individual_id);


    @Select("SELECT  count(id) value, HOUR(alerm_time) hour " +
            "FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND  DATE_FORMAT(alerm_time,'%Y-%m-%d')= #{day} " +
            "GROUP BY HOUR(alerm_time) " +
            "ORDER BY HOUR(alerm_time) DESC")
    @Results(value = {
            @Result(column = "value", property = "value", javaType = Integer.class),
            @Result(column = "hour", property = "hour", javaType = Integer.class)
    })
    List<Map>  queryByDay(@Param("individual_id") String individual_id, @Param("day") String day);

    /**
     * 按年查看总数
     *
     * @param individual_id
     * @param alerm_state
     * @param year
     * @return
     */
    @Select("<script>" +
            "SELECT count(id) FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND YEAR(alerm_time)= #{year} " +
            "<if test='alerm_state!=null'> " +
            "AND alerm_state = #{alerm_state} " +
            "</if> " +
            "<if test='alerm_state==null'> " +
            "AND (trim(alerm_state) != '') " +
            "</if> " +
            "</script>"
    )
    Integer queryByYearTotal(@Param("individual_id") String individual_id, @Param("alerm_state") Integer alerm_state, @Param("year") String year);

    /**
     * 按月查看总数
     *
     * @param individual_id
     * @param alerm_state
     * @param timeStr
     * @return
     */
    @Select("<script>" +
            "SELECT count(id) FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND DATE_FORMAT(alerm_time,'%Y-%m')= #{timeStr} " +
            "<if test='alerm_state!=null'> " +
            "AND alerm_state = #{alerm_state} " +
            "</if> " +
            "<if test='alerm_state==null'> " +
            "AND (trim(alerm_state) != '') " +
            "</if> " +
            "</script>"
    )
    Integer queryByMonthTotal(@Param("individual_id") String individual_id, @Param("alerm_state") Integer alerm_state, @Param("timeStr") String timeStr);

    /**
     * 按天查看总数
     *
     * @param individual_id
     * @param alerm_state
     * @param timeStr
     * @return
     */
    @Select("<script>" +
            "SELECT count(id) FROM thkj_face_stranger " +
            "WHERE individual_id = #{individual_id} " +
            "AND DATE_FORMAT(alerm_time,'%Y-%m-%d')= #{timeStr} " +
            "<if test='alerm_state!=null'> " +
            "AND alerm_state = #{alerm_state} " +
            "</if> " +
            "<if test='alerm_state==null'> " +
            "AND (trim(alerm_state) != '') " +
            "</if> " +
            "</script>"
    )
    Integer queryByDayTotal(@Param("individual_id") String individual_id, @Param("alerm_state") Integer alerm_state, @Param("timeStr") String timeStr);

}

